import openpyxl

# 练习一：获取表中涉及哪几所学校，并统计数量。
# workbook = openpyxl.load_workbook(r'..\Day27-data\student-score.xlsx')
# # print(workbook, workbook.sheetnames)
# sheet = workbook['成绩表']
# col = sheet['C'][2:]
# # school_data = col[2::]
# # # print(school_data)
# # schools = []
# # for cell in school_data:
# #     if cell.value not in schools:
# #         # print(cell.value)
# #         schools.append(cell.value)
# # print(schools, len(schools))
# school_name = [cell.value for cell in col]
# # print(school_name)
# school_name = set(school_name)
# print(school_name, len(school_name))

# 练习二：数据整理
# 文件薄
workbook = openpyxl.load_workbook(r'..\Day27-data\缁冧範鏁版嵁.xlsx')

# 文件表
sheet = workbook['工作表1']

# print(sheet['A1'].value)
a1_data = sheet['A1'].value
data_list = a1_data.split(';')
# print(data_list)
for i, row in enumerate(data_list):
    row_list = row.split(':')
    # print(row_list)
    row_list[0] = row_list[0].strip()
    row_list[1] = row_list[1].strip()
    data_list[i] = row_list

print(data_list)

new_wb = openpyxl.Workbook()  # 新文件薄
print(new_wb.sheetnames)
new_wb_sheet = new_wb['Sheet']
new_wb_sheet.title = 'A1处理结果'

new_wb_sheet['A1'] = '日期'
new_wb_sheet['B1'] = '销售额'
for row in data_list:
    new_wb_sheet.append(row)

new_wb.save(r'..\Day27-data\整理完成数据.xlsx')

